package com.xms.datacore.service;

import com.xms.core.model.PagedList;
import com.xms.datacore.DataBaseSetting;
import com.xms.datacore.DataBaseType;
import com.xms.datacore.SqlDescriptor;
import com.xms.datacore.provider.IDataBase;
import com.xms.datacore.provider.IDataRepositoryBase;
import com.xms.sdk.core.DynamicData;
import com.xms.utils.CollectionUtil;
import com.xms.utils.ResultSetUtil;
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;

/**
 * <p>
 * 自定义SQL查询服务
 * </p>
 *
 * @author migo
 * @since 2021/9/4
 */
@Service
public class DataRepositoryBase implements IDataRepositoryBase {
    private IDataBase _dataBase;

    @Autowired
    public void DataRepositoryBase(IDataBase dataBase) {
        _dataBase = dataBase;
    }

    @Override
    public boolean execute(SqlDescriptor sqlDescriptor) throws SQLException {
        return _dataBase.execute(sqlDescriptor);
    }

    @Override
    public List<DynamicData> query(SqlDescriptor sqlDescriptor) throws SQLException {
        SqlSession session = _dataBase.getSqlSession();
        try {
            //根据语句及参数执行
            ResultSet resultSet = _dataBase.executeQuery(session, sqlDescriptor);
            //resultset 转换为 map list
            List<DynamicData> result = ResultSetUtil.toListDynamicData(resultSet);
            resultSet.close();
            return result;
        } catch (Exception e) {
            throw e;
        } finally {
            _dataBase.closeSqlSession(session);
        }
    }

    @Override
    public PagedList<DynamicData> page(int page, int pageSize, SqlDescriptor sqlDescriptor) throws Exception {
        String sql = sqlDescriptor.getFinalSql();
        Object[] args = sqlDescriptor.getFinalArgs();
        page = (page <= 0 ? 1 : page);
        SqlDescriptor pageSql;// =SqlDescriptor.create().append(sql, args);
        //pageSql.append(" limit ? offset ?", pageSize, (page-1)*pageSize);
        pageSql = this.generatePagingSql(page, pageSize, sqlDescriptor);
        SqlDescriptor totalSql = SqlDescriptor.create().append("select count(1) as total from (" + sql + ") as a", args);
        SqlSession session = _dataBase.getSqlSession();
        try {
            //resultset有两个结果集，一个是数据列表需要转换为 map list，另一个是数据总数
            List<ResultSet> multiRs = _dataBase.executeMultiQuery(session, pageSql, totalSql);
            //根据语句及参数执行
            ResultSet resultSet = multiRs.get(0);
            List<DynamicData> result = ResultSetUtil.toListDynamicData(resultSet);
            resultSet.close();
            PagedList<DynamicData> pageResult = new PagedList<>(page, pageSize, 0);
            pageResult.items = result;
            //总数
            ResultSet totalRs = multiRs.get(1);
            if (totalRs.next()) {
                int total = totalRs.getInt(1);
                pageResult.totalitems = total;
                pageResult.totalpages = total / pageSize + 1;
                totalRs.close();
            }
            return pageResult;
        } catch (Exception e) {
            throw e;
        } finally {
            _dataBase.closeSqlSession(session);
        }
    }

    @Override
    public DynamicData one(SqlDescriptor sqlDescriptor) throws Exception {
        SqlSession session = _dataBase.getSqlSession();
        try {
            //根据语句及参数执行
            ResultSet resultSet = _dataBase.executeQuery(session, sqlDescriptor);
            //resultset 转换为 map list
            DynamicData result = ResultSetUtil.toDynamicData(resultSet);
            resultSet.close();
            return result;
        } catch (Exception e) {
            throw e;
        } finally {
            _dataBase.closeSqlSession(session);
        }
    }

    @Override
    public boolean exists(SqlDescriptor sqlDescriptor) throws SQLException {
        //根据语句及参数执行
        ResultSet resultSet = _dataBase.executeQuery(sqlDescriptor);
        boolean result = resultSet.next();
        resultSet.close();
        return result;
    }

    private SqlDescriptor generatePagingSql(int page, int pageSize, SqlDescriptor sqlDescriptor) {
        SqlDescriptor pagingSql = null;// = SqlDescriptor.create();
        if (DataBaseSetting.dataBaseType.equals(DataBaseType.SQLSERVER)) {
            List<Object> args = new LinkedList<>();
            if (CollectionUtil.notEmpty(sqlDescriptor.getFinalArgs())) {
                args.addAll(Arrays.asList(sqlDescriptor.getFinalArgs()));
            }
            args.add((page - 1) * pageSize);
            args.add((page - 1) * pageSize + pageSize);
            pagingSql = new SqlDescriptor("select * from (" + sqlDescriptor.getFinalSql() + ") a where rownum between ? and ?", args);
        } else {
            pagingSql = new SqlDescriptor(sqlDescriptor.getFinalSql(), sqlDescriptor.getFinalArgs());
            pagingSql.append(" limit ? offset ?", pageSize, (page - 1) * pageSize);
        }
        return pagingSql;
    }
}
